package com.mapfinal.server.mgrdb;

import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Record;
import com.lambkit.common.util.DateTimeUtils;
import com.lambkit.db.meta.ColumnMeta;
import com.lambkit.db.meta.TableMeta;
import com.lambkit.db.mgr.BaseMgrdbService;
import com.lambkit.db.mgr.IField;
import com.lambkit.db.mgr.IFieldDao;
import com.lambkit.db.mgr.ITable;
import com.lambkit.db.mgr.ITableDao;
import com.lambkit.db.mgr.MgrConstants;
import com.lambkit.db.mgr.MgrTable;
import com.mapfinal.server.kit.JXLExcelUtils;
import com.mapfinal.server.mgrdb.model.MfTable;
import com.mapfinal.server.mgrdb.model.MfTableField;

public class MapfinalMgrdbServiceImpl extends BaseMgrdbService {

	@Override
	public ITableDao getTableDao() {
		// TODO Auto-generated method stub
		return MfTable.service();
	}

	@Override
	public IFieldDao getFieldDao() {
		// TODO Auto-generated method stub
		return MfTableField.service();
	}
	
	@Override
	public void tableToMgrdb(TableMeta tbc) {
		// TODO Auto-generated method stub
		tableToMgrdb(tbc, null);
	}
	
	@Override
	public void tableToMgrdb(TableMeta tbc, Map<String, Object> options) {
		// TODO Auto-generated method stub
		ITable model = getTableDao().getAddOrEditModel(tbc.getName());
		if(model==null) System.out.println("model is null");
		model.setAttr("name", tbc.getName());
		if(StrKit.isBlank(model.getTitle())) {
			if(StrKit.notBlank(tbc.getTitle())) model.setAttr("title", tbc.getTitle());
			else model.setAttr("title", tbc.getName());
		}
		MfTable table = (MfTable) model;
		if(StrKit.isBlank(table.getAlias())) {
			if(StrKit.notBlank(tbc.getAttrName())) table.setAlias(tbc.getAttrName());
		}
		if(StrKit.isBlank(table.getGuid())) {
			if(StrKit.notBlank(tbc.getPrimaryKey())) table.setGuid(tbc.getPrimaryKey());
		}
		if(table.getId() !=null ) table.update();
		else {
//			model.setAttr("isdelete", 0);
//			model.setAttr("isedit", 1);
//			model.setAttr("ispages", 1);
//			model.setAttr("isallsel", 1);
//			model.setAttr("isorder", 1);
//			model.setAttr("isdiycol", 0);
//			table.setAttr("orders", 0);
//			table.setAttr("status", 0);
//			java.sql.Timestamp ts = DateTimeUtils.getCurrentTime();
//			table.setAttr("ctime", ts);
//			table.setAttr("mtime", ts);
//			long time = System.currentTimeMillis();
//			table.setCreated(time);
			if(options.containsKey("storeid")) {
				table.setStoreid((Integer) options.get("storeid"));
			} else {
				table.setStoreid(0);
			}
			table.save();
		}
		for(ColumnMeta col : tbc.getColumnMetas()) {
			columnToMgrdb(col, tbc.getName(), model.getId(), tbc.getPrimaryKey());
		}
	}
	
	public void columnToMgrdb(ColumnMeta col, String tbname, Object tbid, String pkname) {
		IField model = getFieldDao().findFirstByTbName(tbname, MgrConstants.NONE, "name", col.getName());
		if(model!=null) {
			MfTableField fld = (MfTableField) model;
			fld.setDatatype(col.getType());
//			fld.setPkey(col.isPrimaryKey() ? 1 : 0);
//			fld.setUnsigned(col.isSigned() ? 0 : 1);
//			fld.setAi(col.isAutoInctement() ? 1 : 0);
//			fld.setNullable(col.getIsNullableType()==1 ? 1 : 0);
			fld.setIskey(col.isPrimaryKey() ? "Y" : "N");
			fld.setIsunsigned(col.isSigned() ? "N" : "Y");
			fld.setIsai(col.isAutoInctement() ? "Y" : "N");
			fld.setIsnullable(col.getIsNullableType()==1 ? "Y" : "N");
			//fld.setFlddefault(col.getDefaultValue());
			//fld.setDiyname(col.getUpperName());
			//if(StrKit.isBlank(fld.getDescript())) fld.setDescript(col.getRemarks());
			fld.setJtype(col.getJavaType());
			if(StrKit.isBlank(fld.getAlias())) fld.setAlias(col.getAttrName());
			fld.update();
		} else {
			IField field = getFieldDao().getAddOrEditModel(null, MgrConstants.NONE);
			MfTableField fld = (MfTableField) field;
			fld.setName(col.getName());
			fld.setTbid((Integer)tbid);
			fld.setTitle(col.getTitle());
			fld.setDatatype(col.getType());
//			fld.setPkey(col.isPrimaryKey() ? 1 : 0);
//			fld.setUnsigned(col.isSigned() ? 0 : 1);
//			fld.setAi(col.isAutoInctement() ? 1 : 0);
//			fld.setNullable(col.getIsNullableType()==1 ? 1 : 0);
			fld.setIskey(col.isPrimaryKey() ? "Y" : "N");
			fld.setIsunsigned(col.isSigned() ? "N" : "Y");
			fld.setIsai(col.isAutoInctement() ? "Y" : "N");
			fld.setIsnullable(col.getIsNullableType()==1 ? "Y" : "N");
			
			//fld.setFlddefault(col.getDefaultValue());
			//fld.setDiyname(col.getUpperName());
			//fld.setDescript(col.getRemarks());
			fld.setJtype(col.getJavaType());
			fld.setAlias(col.getAttrName());
			if(col.isPrimaryKey()) {
				fld.setIsview("Y");
				fld.setIsedit("N");
				fld.setIsselect("Y");
				fld.setIsmustfld("N");
			} else if("blob".equalsIgnoreCase(col.getType())
					|| "geometry".equalsIgnoreCase(col.getType())
					|| "serial".equalsIgnoreCase(col.getType())
					|| "json".equalsIgnoreCase(col.getType())
					|| "jsonb".equalsIgnoreCase(col.getType())) {
			} else {
				fld.setIsview("Y");
				fld.setIsedit("Y");
				fld.setIsselect("Y");
				fld.setIsmustfld("Y");
			}
			
			fld.save();
		}
	}

	@Override
	public String getTableTableName() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getFieldTableName() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public MgrTable createByAlias(String alias, int type, String orderby) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void toExcel(MgrTable tbc, OutputStream os, List<Record> mlist, String selectSQL) {
		// TODO Auto-generated method stub
		List<? extends IField> flds = tbc.getFieldList();
		JXLExcelUtils exl = new JXLExcelUtils();
		int c = 0, r = 0;
		selectSQL = selectSQL.replace(" ", "");
		if (selectSQL.equals("*")) {
			String fldnames = "";
			for (int i = 0; i < flds.size(); i++) {
				fldnames += "," + settingNameOfDialect(flds.get(i).getName(), tbc.getDialect());
			}
			selectSQL = fldnames.substring(1);
		}
		exl.createWorkBook(os).createSheet(tbc.getModel().getStr("tbcnn"), 0);
		// boolean ball = false;
		// if(selectSQL.equals("*")) ball = true;
		// else selectSQL += ",";
		selectSQL += ",";
		for (IField fld : flds) {
			if (fld.getStr("ext01").equals("N"))
				continue;
			if (selectSQL.contains(fld.getName() + ",")) {
				exl.addCell(c, r, fld.getTitle());
				c++;
			}
		}
		c = 0;
		r++;
		for (Record m : mlist) {
			for (IField fld : flds) {
				if (fld.getStr("ext01").equals("N"))
					continue;
				if (selectSQL.contains(fld.getName() + ",")) {
					addCell(exl, m, fld, c, r, fld.getName());
					c++;
				}
			}
			c = 0;
			r++;
		}
		exl.export();
		// return tbcnn;
	}

	private void addCell(JXLExcelUtils exl, Record m, IField fld, int c, int r, String name) {
		if (m.get(name) == null)
			exl.addCell(c, r, "");
		else if (fld.getDatatype().startsWith("int"))
			exl.addCell(c, r, m.getInt(name));
		else if (fld.getDatatype().startsWith("long"))
			exl.addCell(c, r, m.getLong(name));
		else if (fld.getDatatype().startsWith("float"))
			exl.addCell(c, r, m.getFloat(name));
		else if (fld.getDatatype().startsWith("double"))
			exl.addCell(c, r, m.getDouble(name));
		else if (fld.getDatatype().startsWith("varchar"))
			exl.addCell(c, r, m.getStr(name));
		else if (fld.getDatatype().startsWith("timestamp"))
			exl.addCell(c, r, m.getDate(name));
		else
			exl.addCell(c, r, m.get(name).toString());
	}

	

}
